package com.foreveredu.propms.persist.hibernate;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.foreveredu.propms.domain.District;
import com.foreveredu.propms.persist.IDistrictDao;


public class HibernateDistrictDao extends HibernateAbstractDao<District, Integer>
		implements IDistrictDao {

	private static final long serialVersionUID = 1L;

	public HibernateDistrictDao() {
		super(District.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public District findDistrictByDistrictId(String districtId) {
		List<District> districtList = this.getHibernateTemplate().find("from District district where district.districtId = ?", districtId);
		District district = null;
		if (districtList != null && districtList.size() > 0) {
			district = districtList.get(0);
		}
		return district;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public District findDistrictId(int id) {
		List<District> districtList = this.getHibernateTemplate().find("from District district where district.id = ?", id);
		District district = null;
		if (districtList != null && districtList.size() > 0) {
			district = districtList.get(0);
		}
		return district;
	}

	@Override
	public Map<String, Object> queryDistMonthlyCharge(District district,
			int year, int month) {
		Map<String, Object> queryMap = new HashMap<String, Object>();
		Connection conn = this.getSession().connection();
		CallableStatement cstmt;
		try{
			cstmt = conn.prepareCall("{call proc_queryDistMonthlyCharge(?,?,?,?,?,?,?,?,?,?,?,?)}");
			cstmt.setInt(1, district.getId());
			cstmt.setInt(2, year);
			cstmt.setInt(3, month);
			cstmt.registerOutParameter(4, Types.FLOAT);
			cstmt.registerOutParameter(5, Types.FLOAT);
			cstmt.registerOutParameter(6, Types.FLOAT);
			cstmt.registerOutParameter(7, Types.FLOAT);	
			cstmt.registerOutParameter(8, Types.FLOAT);
			cstmt.registerOutParameter(9, Types.FLOAT);
			cstmt.registerOutParameter(10, Types.FLOAT);
			cstmt.registerOutParameter(11, Types.FLOAT);
			cstmt.registerOutParameter(12, Types.FLOAT);				
			cstmt.execute();
			queryMap.put("totalSum", cstmt.getFloat(4));
			queryMap.put("paidSum", cstmt.getFloat(5));
			queryMap.put("propCharge", cstmt.getFloat(6));
			queryMap.put("powerCharge", cstmt.getFloat(7));
			queryMap.put("waterCharge", cstmt.getFloat(8));
			queryMap.put("carCharge", cstmt.getFloat(9));
			queryMap.put("rubbishCharge", cstmt.getFloat(10));
			queryMap.put("lightCharge", cstmt.getFloat(11));
			queryMap.put("water2ndCharge", cstmt.getFloat(12));
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return queryMap;
	}
    
	@Override
	public Map<String, Object> queryDistYearCharge(District district, int year) {
		Map<String, Object> queryMap = new HashMap<String, Object>();
		Connection conn = this.getSession().connection();
		CallableStatement cstmt;
		try{
			cstmt = conn.prepareCall("{call proc_queryDistYearCharge(?,?,?,?,?,?,?,?,?,?,?)}");
			cstmt.setInt(1, district.getId());
			cstmt.setInt(2, year);
			cstmt.registerOutParameter(3, Types.FLOAT);
			cstmt.registerOutParameter(4, Types.FLOAT);
			cstmt.registerOutParameter(5, Types.FLOAT);
			cstmt.registerOutParameter(6, Types.FLOAT);	
			cstmt.registerOutParameter(7, Types.FLOAT);
			cstmt.registerOutParameter(8, Types.FLOAT);
			cstmt.registerOutParameter(9, Types.FLOAT);
			cstmt.registerOutParameter(10, Types.FLOAT);	
			cstmt.registerOutParameter(11, Types.FLOAT);				
			cstmt.execute();
			queryMap.put("totalSum", cstmt.getFloat(3));
			queryMap.put("paidSum", cstmt.getFloat(4));
			queryMap.put("propCharge", cstmt.getFloat(5));
			queryMap.put("powerCharge", cstmt.getFloat(6));
			queryMap.put("waterCharge", cstmt.getFloat(7));
			queryMap.put("carCharge", cstmt.getFloat(8));
			queryMap.put("rubbishCharge", cstmt.getFloat(9));
			queryMap.put("lightCharge", cstmt.getFloat(10));
			queryMap.put("water2ndCharge", cstmt.getFloat(11));
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return queryMap;
	}
	
}













